import random
import csv
import numpy as np
from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook
# from openpyxl.compat import range
from openpyxl.utils import get_column_letter
import json


'''
    加载excel 多行多列    
    # 从第几行开始
'''
def load_xl_muti(fn,start = 2):
    # 读取excel2007文件
    wb = load_workbook(filename=fn)

    # 显示有多少张表
    print("Worksheet range(s):", wb.get_named_ranges())
    print("Worksheet name(s):", wb.get_sheet_names())

    # 取第一张表
    sheetnames = wb.get_sheet_names()
    ws = wb.get_sheet_by_name(sheetnames[0])

    # 显示表名，表行数，表列数
    print("Work Sheet Titile:", ws.title)
    print("Work Sheet Rows:", ws.max_row)
    print("Work Sheet Cols:", ws.max_column)
    # 建立存储数据的字典
    data_dic = []


    # 把数据存到字典中
    for rx in range(start, ws.max_row + 1):
        temp_list = []
        pid = rx
        for col in range(1, ws.max_column + 1):
            w = ws.cell(row=rx, column=col).value
            temp_list.append(w)
        data_dic.append(temp_list)
    random.shuffle(data_dic)
    random.shuffle(data_dic)
    # 打印字典数据个数
    print('Total:%d' % len(data_dic))
    return data_dic

def load_txt(fn):
    # data = np.loadtxt("data.txt")  # 将文件中数据加载到data数组里
    #第三种方法
    with open(fn,"r")as f:   #设置文件对象
        data = f.readlines()  #直接将文件中按行读到list里，效果与方法2一样
    print(len(data))
    # for i in data:  # 对于双层列表中的数据
    #     i = i[:-1]
    #     print(str(i))
    random.shuffle(data)
    random.shuffle(data)
    return data

def load_csv(fn):
    # data = np.loadtxt("data.txt")  # 将文件中数据加载到data数组里
    data=[]
    #第三种方法
    with open(fn,"r",encoding='utf-8')as f:   #设置文件对象
        fcsv = csv.reader(f)  #直接将文件中按行读到list里，效果与方法2一样
        # print(len(fcvs))
        # heads=next(fcsv)
        for row in fcsv:
            # print(str(row))
            data.append(row)
    # for i in data:  # 对于双层列表中的数据
    #     i = i[:-1]
    #     print(str(i))
    print(len(data))
    random.shuffle(data)
    random.shuffle(data)
    print(len(data))
    return data




def save_txt_to_excel(txt_data):
    wb = Workbook()
    ws1 = wb.active
    # ws1.title = "range names"
    ws1.append(head)
    count = 0
    num = 0
    for d in txt_data:  # 对于双层列表中的数据
        # ws1.append(list(user_info[0],user_info[1],str(d).strip()))
        user_info[2] = str(d).strip()
        ws1.append(user_info)
        count += 1
        if count >= 500000:
            count = 0
            num += 1
            wb.save(filename=dest_filename + "_" + str(num) + dest_ext)
            wb = Workbook()
            ws1 = wb.active
            # ws1.title = "range names"
            ws1.append(head)
    if count > 0:
        num += 1  # 保存最后一页
        wb.save(filename=dest_filename + "_" + str(num) + dest_ext)

''' 数据只有一列的 '''
def save_1col_to_excel(data_dic):
    wb = Workbook()
    ws1 = wb.active
    # ws1.title = "range names"
    ws1.append(head)
    count = 0
    num=0
    for d in data_dic:  # 对于双层列表中的数据
        # ws1.append(list(user_info[0],user_info[1],str(d).strip()))
        user_info[2] = str(d[0]).strip()
        ws1.append(user_info)
        count+=1
        if count>=500000:
            count = 0
            num+=1
            wb.save(filename=dest_filename+"_"+str(num)+dest_ext)
            wb = Workbook()
            ws1 = wb.active
            # ws1.title = "range names"
            ws1.append(head)

    if count > 0:
        num += 1 #保存最后一页
        wb.save(filename=dest_filename + "_" + str(num) + dest_ext)

''' 数据只有3列的 '''
def save_3col_to_excel(data_dic):
    wb = Workbook()
    ws1 = wb.active
    # ws1.title = "range names"
    ws1.append(head)
    count = 0
    num = 0
    for d in data_dic:  # 对于双层列表中的数据
        # ws1.append(list(user_info[0],user_info[1],str(d).strip()))
        # user_info[2] = str(d[0]).strip()
        ws1.append(d)
        count += 1
        if count >= 500000:
            count = 0
            num += 1
            wb.save(filename=dest_filename + "_" + str(num) + dest_ext)
            wb = Workbook()
            ws1 = wb.active
            # ws1.title = "range names"
            ws1.append(head)

    if count > 0:
        num += 1  # 保存最后一页
        wb.save(filename=dest_filename + "_" + str(num) + dest_ext)

# data_dic=load_xl_muti(r'18379.xlsx')
# print(json.dumps(data_dic, ensure_ascii=False))
#user_info = ["261", "szjjgateway_01",""]
#user_info = ["0", "18375",""]
#user_info = ["0", "5234sfjx",""]
#user_info = ["0", "500094",""]
#user_info = ["623", "szszzgha",""]
#user_info = ["333", "szrmyy",""]
#user_info = ["0", "68601",""]
#user_info = ["611", "czsbwxxcx",""]
#user_info = ["567", "szbdyy",""]
user_info = ["2036", "szzdyy",""]
dest_filename = user_info[1]+'_ls_random'
dest_ext='.xlsx'
head = ["eprId", "uid", "mobile"]


#fn = r'总工会（原rmyy数据）_215731条.txt'
#fn = r'4.17_18375_454998条.txt'
# fn = r'18375_2.txt'
# fn = r'18379.xlsx'
#fn = r'减去szrmyy12月发送成功号码.csv' #人民医院
#fn = r'10468&10469&10467四月份deliver的深圳号码.csv' #人民医院
#fn = r'四月szhw02非北上广深&江苏&浙江&佛山状态为deliver的.csv' #18375
#fn = r'18375保险移动号码（除大省和广东部分城市）_3.txt' #18375
#fn = r'18375广西一部分.txt' #18375
#fn = r'68601_5234bjgs四月份成功号码.csv' #68601
#fn = r'68601_lgqyqfkzhb&szrmyy五月份发送成功号码.csv' #68601
#fn = r'czsbwxxcx——潮汕地区成功号码.csv' #czsbwxxcx
#fn = r'深圳三网非szbdyy账号发送号码.csv' #szbdyy
fn = r'深圳三网非szzdyy账号发送号码.csv' #szbdyy

if fn.endswith(".txt"):
    txt_data = load_txt(fn)
    save_txt_to_excel(txt_data)
elif fn.endswith(".xlsx"):
    start = 2
    data_dic = load_xl_muti(fn,start)
    if(len(data_dic[0])==1):
        save_1col_to_excel(data_dic)
    else:
        save_3col_to_excel(data_dic)
elif fn.endswith(".csv"):
    data_dic = load_csv(fn)
    if(len(data_dic[0])==1):
        save_1col_to_excel(data_dic)
    else:
        save_3col_to_excel(data_dic)


